package com.etc.traval.daoimpl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.etc.traval.dao.PlaceDao;
import com.etc.traval.dao.StrategyDao;
import com.etc.traval.dao.UserDao;
import com.etc.traval.util.JDBCUtil;
import com.etc.travel.entity.Place;
import com.etc.travel.entity.Strategy;

import oracle.jdbc.internal.OracleTypes;

public class StrategyDaoImpl implements StrategyDao {

	JDBCUtil util = new JDBCUtil();
	UserDao dao = new UserDaoImpl();
	PlaceDao dao1 = new PlaceDaoImpl();

	/**
	 * 添加攻略的方法
	 * 
	 * @author 刘一清
	 * @return 返回Boolean值：是否添加成功
	 * @param 传入一个要添加的Strategy对象
	 * @date 2018年5月19日 10时15分39秒
	 */
	@Override
	public boolean strategyAdd(Strategy strategy) {
		boolean flag = util.callNotRetrun(
				"insert into t_strategy (strategy_title,strategy_time,strategy_user,strategy_content,strategy_state) values(?,?,?,?,?)",
				strategy.getStrategy_title(), strategy.getStrategy_time(), strategy.getStrategy_user().getUser_id(),
				strategy.getStrategy_content(), strategy.getStrategy_state());
		return flag;
	}

	/**
	 * 查询所有攻略的方法
	 * 
	 * @author 刘一清
	 * @param 传入页码和每页显示的条数
	 * @return 返回所有Strategy对象的集合
	 * @date 2018年5月19日 10时35分26秒
	 */
	@Override
	public List<Strategy> queryAllStrategy(int page, int size) {
		ResultSet rs = util.query(
				"select t.*,t.a from (select s.*,rownum as a  from (select * from t_strategy where strategy_state='已通过') s where rownum<=?) t where t.a>? order by strategy_time desc",
				page * size, (page - 1) * size);
		List<Strategy> list = new ArrayList<Strategy>();
		try {
			while (rs.next()) {
				Strategy strategy = new Strategy();
				strategy.setStrategy_id(rs.getInt("strategy_id"));
				strategy.setStrategy_title(rs.getString("strategy_title"));
				strategy.setStrategy_time(rs.getTimestamp("strategy_time"));
				strategy.setStrategy_user(dao.queryUserById(rs.getInt("strategy_user")));
				strategy.setStrategy_content(rs.getString("strategy_content"));
				strategy.setStrategy_state(rs.getString("strategy_state"));
				strategy.setStrategy_place(dao1.queryPlaceById(rs.getInt("strategy_place")));
				list.add(strategy);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			util.closeAll();
		}
		return list;
	}

	/**
	 * 根据id查找攻略
	 * 
	 * @author 刘一清
	 * @param 传入要查找的id
	 * @return 返回查找到的Strategy对象
	 * @date 2018年5月29日 14时27分12秒
	 */
	@Override
	public Strategy querySTrategyById(int id) {
		List<Integer> list = new ArrayList<Integer>();
		list.add(OracleTypes.CURSOR);
		Object[] obj = util.callHasReturn("{call proc_querystrategybyid(?,?)}", list, id);
		ResultSet rs = (ResultSet) obj[0];
		Strategy strategy = new Strategy();
		try {
			if (rs.next()) {
				strategy.setStrategy_id(rs.getInt("strategy_id"));
				strategy.setStrategy_title(rs.getString("strategy_title"));
				strategy.setStrategy_time(rs.getTimestamp("strategy_time"));
				strategy.setStrategy_user(dao.queryUserById(rs.getInt("strategy_user")));
				
				strategy.setStrategy_content(rs.getString("strategy_content"));
				strategy.setStrategy_state(rs.getString("strategy_state"));
				if(rs.getInt("strategy_place")!=0){
				strategy.setStrategy_place(dao1.queryPlaceById(rs.getInt("strategy_place")));}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				util.closeAll();
			} catch (Exception e2) {
			}
		}
		return strategy;
	}

	/**
	 * 根据景点查找攻略
	 * 
	 * @author 刘一清
	 * @param 传入要查找的景点place对象
	 * @return 返回查找到的Strategy对象
	 * @date 2018年5月29日 15时41分25秒
	 */
	@Override
	public List<Strategy> querySTrategyByPlace(Place place, int page, int size) {
		ResultSet rs = util.query(
				"select t.*,t.a from (select s.*,rownum as a  from (select * from t_strategy where strategy_place=?) s where rownum<=?) t where t.a>?",
				place.getPlace_id(), page * size, (page - 1) * size);
		List<Strategy> list = new ArrayList<Strategy>();
		try {
			while (rs.next()) {
				Strategy strategy = new Strategy();
				strategy.setStrategy_id(rs.getInt("strategy_id"));
				strategy.setStrategy_title(rs.getString("strategy_title"));
				strategy.setStrategy_time(rs.getTimestamp("strategy_time"));
				strategy.setStrategy_user(dao.queryUserById(rs.getInt("strategy_user")));
				strategy.setStrategy_content(rs.getString("strategy_content"));
				strategy.setStrategy_state(rs.getString("strategy_state"));
				strategy.setStrategy_place(dao1.queryPlaceById(rs.getInt("strategy_place")));
				list.add(strategy);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				util.closeAll();
			} catch (Exception e2) {
			}
		}
		return list;
	}

	/**
	 * 根据id删除攻略
	 * 
	 * @author 刘一清
	 * @param 传入要删除的id
	 * @return 返回是否删除成功
	 * @date 2018年5月29日 15时46分23秒
	 */
	@Override
	public boolean deleteStrategyById(int id) {
		return util.update("delete from t_strategy where strategy_id=?", id) > 0;
	}

	/**
	 * 查找还未通过审核的攻略
	 * @author cjw
	 */
	@Override
	public List<Strategy> queryAllStrategyByAdmin(int page, int size) {
		ResultSet rs = util.query(
				"select t.*,t.a from (select s.*,rownum as a  from (select * from t_strategy where strategy_state='未通过' order by strategy_time desc) s where rownum<=?) t where t.a>?",
				page * size, (page - 1) * size);
		List<Strategy> list = new ArrayList<Strategy>();
		try {
			while (rs.next()) {
				Strategy strategy = new Strategy();
				strategy.setStrategy_id(rs.getInt("strategy_id"));
				strategy.setStrategy_title(rs.getString("strategy_title"));
				strategy.setStrategy_time(rs.getTimestamp("strategy_time"));
				strategy.setStrategy_user(dao.queryUserById(rs.getInt("strategy_user")));
				strategy.setStrategy_content(rs.getString("strategy_content"));
				strategy.setStrategy_state(rs.getString("strategy_state"));
				strategy.setStrategy_place(dao1.queryPlaceById(rs.getInt("strategy_place")));
				list.add(strategy);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			util.closeAll();
		}
		return list;
	}

	@Override
	public boolean updatestrategyok(int id) {
		int i= util.update("update t_strategy set strategy_state='已通过' where strategy_id=?", id);
		return i>0;
	}

}
